
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/25/2013 14:27:31
-- Generated from EDMX file: F:\Dropbox\CurrProject\OOD\QLHocVu\DataAccessObject\QLHVModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [QLHV];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CHITIETMONHOC_KHOILOP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CHITIETMONHOCs] DROP CONSTRAINT [FK_CHITIETMONHOC_KHOILOP];
GO
IF OBJECT_ID(N'[dbo].[FK_CHITIETMONHOC_MONHOC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CHITIETMONHOCs] DROP CONSTRAINT [FK_CHITIETMONHOC_MONHOC];
GO
IF OBJECT_ID(N'[dbo].[FK_LOPHOC_GIAOVIEN]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LOPHOCs] DROP CONSTRAINT [FK_LOPHOC_GIAOVIEN];
GO
IF OBJECT_ID(N'[dbo].[FK_LOPHOC_KHOILOP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LOPHOCs] DROP CONSTRAINT [FK_LOPHOC_KHOILOP];
GO
IF OBJECT_ID(N'[dbo].[FK_LOPHOC_NAMHOC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LOPHOCs] DROP CONSTRAINT [FK_LOPHOC_NAMHOC];
GO
IF OBJECT_ID(N'[dbo].[FK_CHITIETLOPHOC_HOCSINH]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CHITIETLOPHOC] DROP CONSTRAINT [FK_CHITIETLOPHOC_HOCSINH];
GO
IF OBJECT_ID(N'[dbo].[FK_CHITIETLOPHOC_LOPHOC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CHITIETLOPHOC] DROP CONSTRAINT [FK_CHITIETLOPHOC_LOPHOC];
GO
IF OBJECT_ID(N'[dbo].[FK_USER_ROLE_ROLE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[USER_ROLE] DROP CONSTRAINT [FK_USER_ROLE_ROLE];
GO
IF OBJECT_ID(N'[dbo].[FK_USER_ROLE_USER]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[USER_ROLE] DROP CONSTRAINT [FK_USER_ROLE_USER];
GO
IF OBJECT_ID(N'[dbo].[FK_CHUYENMON_CHITIETMONHOC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CHUYENMON] DROP CONSTRAINT [FK_CHUYENMON_CHITIETMONHOC];
GO
IF OBJECT_ID(N'[dbo].[FK_CHUYENMON_GIAOVIEN]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CHUYENMON] DROP CONSTRAINT [FK_CHUYENMON_GIAOVIEN];
GO
IF OBJECT_ID(N'[dbo].[FK_GIAOVIEN_inherits_USER]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[USERs_GIAOVIEN] DROP CONSTRAINT [FK_GIAOVIEN_inherits_USER];
GO
IF OBJECT_ID(N'[dbo].[FK_HOCSINH_inherits_USER]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[USERs_HOCSINH] DROP CONSTRAINT [FK_HOCSINH_inherits_USER];
GO
IF OBJECT_ID(N'[dbo].[FK_NHANVIEN_inherits_USER]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[USERs_NHANVIEN] DROP CONSTRAINT [FK_NHANVIEN_inherits_USER];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[CHITIETMONHOCs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CHITIETMONHOCs];
GO
IF OBJECT_ID(N'[dbo].[KHOILOPs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[KHOILOPs];
GO
IF OBJECT_ID(N'[dbo].[LOPHOCs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LOPHOCs];
GO
IF OBJECT_ID(N'[dbo].[MONHOCs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MONHOCs];
GO
IF OBJECT_ID(N'[dbo].[NAMHOCs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[NAMHOCs];
GO
IF OBJECT_ID(N'[dbo].[ROLEs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ROLEs];
GO
IF OBJECT_ID(N'[dbo].[USERs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[USERs];
GO
IF OBJECT_ID(N'[dbo].[USERs_GIAOVIEN]', 'U') IS NOT NULL
    DROP TABLE [dbo].[USERs_GIAOVIEN];
GO
IF OBJECT_ID(N'[dbo].[USERs_HOCSINH]', 'U') IS NOT NULL
    DROP TABLE [dbo].[USERs_HOCSINH];
GO
IF OBJECT_ID(N'[dbo].[USERs_NHANVIEN]', 'U') IS NOT NULL
    DROP TABLE [dbo].[USERs_NHANVIEN];
GO
IF OBJECT_ID(N'[dbo].[CHITIETLOPHOC]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CHITIETLOPHOC];
GO
IF OBJECT_ID(N'[dbo].[USER_ROLE]', 'U') IS NOT NULL
    DROP TABLE [dbo].[USER_ROLE];
GO
IF OBJECT_ID(N'[dbo].[CHUYENMON]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CHUYENMON];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'CHITIETMONHOCs'
CREATE TABLE [dbo].[CHITIETMONHOCs] (
    [ChiTietMonHocId] int IDENTITY(1,1) NOT NULL,
    [MonHocId] int  NOT NULL,
    [KhoiLopId] int  NOT NULL
);
GO

-- Creating table 'KHOILOPs'
CREATE TABLE [dbo].[KHOILOPs] (
    [KhoiLopId] int IDENTITY(1,1) NOT NULL,
    [TenKhoiLop] nvarchar(50)  NOT NULL,
    [MoTa] varchar(max)  NULL
);
GO

-- Creating table 'LOPHOCs'
CREATE TABLE [dbo].[LOPHOCs] (
    [LopHocId] int IDENTITY(1,1) NOT NULL,
    [TenLopHoc] nvarchar(50)  NOT NULL,
    [GiaoVienId] int  NOT NULL,
    [KhoiLopId] int  NOT NULL,
    [NamHocId] int  NOT NULL
);
GO

-- Creating table 'MONHOCs'
CREATE TABLE [dbo].[MONHOCs] (
    [MonHocId] int IDENTITY(1,1) NOT NULL,
    [TenMonHoc] nvarchar(50)  NOT NULL,
    [MoTa] varchar(max)  NULL
);
GO

-- Creating table 'NAMHOCs'
CREATE TABLE [dbo].[NAMHOCs] (
    [NamHocId] int IDENTITY(1,1) NOT NULL,
    [TenNamHoc] nvarchar(50)  NOT NULL,
    [MoTa] varchar(max)  NULL
);
GO

-- Creating table 'ROLEs'
CREATE TABLE [dbo].[ROLEs] (
    [RoleId] int IDENTITY(1,1) NOT NULL,
    [RoleName] varchar(50)  NOT NULL
);
GO

-- Creating table 'USERs'
CREATE TABLE [dbo].[USERs] (
    [UserId] int IDENTITY(1,1) NOT NULL,
    [UserName] varchar(50)  NULL,
    [Password] varchar(50)  NULL,
    [HoTen] nvarchar(100)  NOT NULL,
    [NgaySinh] datetime  NOT NULL,
    [GioiTinh] bit  NOT NULL,
    [QueQuan] nvarchar(200)  NOT NULL,
    [DiaChi] nvarchar(200)  NOT NULL
);
GO

-- Creating table 'USERs_GIAOVIEN'
CREATE TABLE [dbo].[USERs_GIAOVIEN] (
    [MaGiaoVien] varchar(50)  NOT NULL,
    [NgayVaoLam] datetime  NOT NULL,
    [NgayNghiViec] datetime  NULL,
    [CMND] varchar(50)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'USERs_HOCSINH'
CREATE TABLE [dbo].[USERs_HOCSINH] (
    [MaHocSinh] varchar(50)  NOT NULL,
    [NgayNhapHoc] datetime  NOT NULL,
    [NgayNghiHoc] datetime  NULL,
    [NguoiBaoHo] nvarchar(100)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'USERs_NHANVIEN'
CREATE TABLE [dbo].[USERs_NHANVIEN] (
    [MaNhanVien] varchar(50)  NOT NULL,
    [NgayVaoLam] datetime  NOT NULL,
    [NgayNghiViec] datetime  NULL,
    [CMND] varchar(50)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'CHITIETLOPHOC'
CREATE TABLE [dbo].[CHITIETLOPHOC] (
    [HOCSINHs_UserId] int  NOT NULL,
    [LOPHOCs_LopHocId] int  NOT NULL
);
GO

-- Creating table 'USER_ROLE'
CREATE TABLE [dbo].[USER_ROLE] (
    [ROLEs_RoleId] int  NOT NULL,
    [USERs_UserId] int  NOT NULL
);
GO

-- Creating table 'CHUYENMON'
CREATE TABLE [dbo].[CHUYENMON] (
    [CHITIETMONHOCs_ChiTietMonHocId] int  NOT NULL,
    [GIAOVIENs_UserId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ChiTietMonHocId] in table 'CHITIETMONHOCs'
ALTER TABLE [dbo].[CHITIETMONHOCs]
ADD CONSTRAINT [PK_CHITIETMONHOCs]
    PRIMARY KEY CLUSTERED ([ChiTietMonHocId] ASC);
GO

-- Creating primary key on [KhoiLopId] in table 'KHOILOPs'
ALTER TABLE [dbo].[KHOILOPs]
ADD CONSTRAINT [PK_KHOILOPs]
    PRIMARY KEY CLUSTERED ([KhoiLopId] ASC);
GO

-- Creating primary key on [LopHocId] in table 'LOPHOCs'
ALTER TABLE [dbo].[LOPHOCs]
ADD CONSTRAINT [PK_LOPHOCs]
    PRIMARY KEY CLUSTERED ([LopHocId] ASC);
GO

-- Creating primary key on [MonHocId] in table 'MONHOCs'
ALTER TABLE [dbo].[MONHOCs]
ADD CONSTRAINT [PK_MONHOCs]
    PRIMARY KEY CLUSTERED ([MonHocId] ASC);
GO

-- Creating primary key on [NamHocId] in table 'NAMHOCs'
ALTER TABLE [dbo].[NAMHOCs]
ADD CONSTRAINT [PK_NAMHOCs]
    PRIMARY KEY CLUSTERED ([NamHocId] ASC);
GO

-- Creating primary key on [RoleId] in table 'ROLEs'
ALTER TABLE [dbo].[ROLEs]
ADD CONSTRAINT [PK_ROLEs]
    PRIMARY KEY CLUSTERED ([RoleId] ASC);
GO

-- Creating primary key on [UserId] in table 'USERs'
ALTER TABLE [dbo].[USERs]
ADD CONSTRAINT [PK_USERs]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'USERs_GIAOVIEN'
ALTER TABLE [dbo].[USERs_GIAOVIEN]
ADD CONSTRAINT [PK_USERs_GIAOVIEN]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'USERs_HOCSINH'
ALTER TABLE [dbo].[USERs_HOCSINH]
ADD CONSTRAINT [PK_USERs_HOCSINH]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'USERs_NHANVIEN'
ALTER TABLE [dbo].[USERs_NHANVIEN]
ADD CONSTRAINT [PK_USERs_NHANVIEN]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [HOCSINHs_UserId], [LOPHOCs_LopHocId] in table 'CHITIETLOPHOC'
ALTER TABLE [dbo].[CHITIETLOPHOC]
ADD CONSTRAINT [PK_CHITIETLOPHOC]
    PRIMARY KEY NONCLUSTERED ([HOCSINHs_UserId], [LOPHOCs_LopHocId] ASC);
GO

-- Creating primary key on [ROLEs_RoleId], [USERs_UserId] in table 'USER_ROLE'
ALTER TABLE [dbo].[USER_ROLE]
ADD CONSTRAINT [PK_USER_ROLE]
    PRIMARY KEY NONCLUSTERED ([ROLEs_RoleId], [USERs_UserId] ASC);
GO

-- Creating primary key on [CHITIETMONHOCs_ChiTietMonHocId], [GIAOVIENs_UserId] in table 'CHUYENMON'
ALTER TABLE [dbo].[CHUYENMON]
ADD CONSTRAINT [PK_CHUYENMON]
    PRIMARY KEY NONCLUSTERED ([CHITIETMONHOCs_ChiTietMonHocId], [GIAOVIENs_UserId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [KhoiLopId] in table 'CHITIETMONHOCs'
ALTER TABLE [dbo].[CHITIETMONHOCs]
ADD CONSTRAINT [FK_CHITIETMONHOC_KHOILOP]
    FOREIGN KEY ([KhoiLopId])
    REFERENCES [dbo].[KHOILOPs]
        ([KhoiLopId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CHITIETMONHOC_KHOILOP'
CREATE INDEX [IX_FK_CHITIETMONHOC_KHOILOP]
ON [dbo].[CHITIETMONHOCs]
    ([KhoiLopId]);
GO

-- Creating foreign key on [MonHocId] in table 'CHITIETMONHOCs'
ALTER TABLE [dbo].[CHITIETMONHOCs]
ADD CONSTRAINT [FK_CHITIETMONHOC_MONHOC]
    FOREIGN KEY ([MonHocId])
    REFERENCES [dbo].[MONHOCs]
        ([MonHocId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CHITIETMONHOC_MONHOC'
CREATE INDEX [IX_FK_CHITIETMONHOC_MONHOC]
ON [dbo].[CHITIETMONHOCs]
    ([MonHocId]);
GO

-- Creating foreign key on [GiaoVienId] in table 'LOPHOCs'
ALTER TABLE [dbo].[LOPHOCs]
ADD CONSTRAINT [FK_LOPHOC_GIAOVIEN]
    FOREIGN KEY ([GiaoVienId])
    REFERENCES [dbo].[USERs_GIAOVIEN]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LOPHOC_GIAOVIEN'
CREATE INDEX [IX_FK_LOPHOC_GIAOVIEN]
ON [dbo].[LOPHOCs]
    ([GiaoVienId]);
GO

-- Creating foreign key on [KhoiLopId] in table 'LOPHOCs'
ALTER TABLE [dbo].[LOPHOCs]
ADD CONSTRAINT [FK_LOPHOC_KHOILOP]
    FOREIGN KEY ([KhoiLopId])
    REFERENCES [dbo].[KHOILOPs]
        ([KhoiLopId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LOPHOC_KHOILOP'
CREATE INDEX [IX_FK_LOPHOC_KHOILOP]
ON [dbo].[LOPHOCs]
    ([KhoiLopId]);
GO

-- Creating foreign key on [NamHocId] in table 'LOPHOCs'
ALTER TABLE [dbo].[LOPHOCs]
ADD CONSTRAINT [FK_LOPHOC_NAMHOC]
    FOREIGN KEY ([NamHocId])
    REFERENCES [dbo].[NAMHOCs]
        ([NamHocId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LOPHOC_NAMHOC'
CREATE INDEX [IX_FK_LOPHOC_NAMHOC]
ON [dbo].[LOPHOCs]
    ([NamHocId]);
GO

-- Creating foreign key on [HOCSINHs_UserId] in table 'CHITIETLOPHOC'
ALTER TABLE [dbo].[CHITIETLOPHOC]
ADD CONSTRAINT [FK_CHITIETLOPHOC_HOCSINH]
    FOREIGN KEY ([HOCSINHs_UserId])
    REFERENCES [dbo].[USERs_HOCSINH]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [LOPHOCs_LopHocId] in table 'CHITIETLOPHOC'
ALTER TABLE [dbo].[CHITIETLOPHOC]
ADD CONSTRAINT [FK_CHITIETLOPHOC_LOPHOC]
    FOREIGN KEY ([LOPHOCs_LopHocId])
    REFERENCES [dbo].[LOPHOCs]
        ([LopHocId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CHITIETLOPHOC_LOPHOC'
CREATE INDEX [IX_FK_CHITIETLOPHOC_LOPHOC]
ON [dbo].[CHITIETLOPHOC]
    ([LOPHOCs_LopHocId]);
GO

-- Creating foreign key on [ROLEs_RoleId] in table 'USER_ROLE'
ALTER TABLE [dbo].[USER_ROLE]
ADD CONSTRAINT [FK_USER_ROLE_ROLE]
    FOREIGN KEY ([ROLEs_RoleId])
    REFERENCES [dbo].[ROLEs]
        ([RoleId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [USERs_UserId] in table 'USER_ROLE'
ALTER TABLE [dbo].[USER_ROLE]
ADD CONSTRAINT [FK_USER_ROLE_USER]
    FOREIGN KEY ([USERs_UserId])
    REFERENCES [dbo].[USERs]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_USER_ROLE_USER'
CREATE INDEX [IX_FK_USER_ROLE_USER]
ON [dbo].[USER_ROLE]
    ([USERs_UserId]);
GO

-- Creating foreign key on [CHITIETMONHOCs_ChiTietMonHocId] in table 'CHUYENMON'
ALTER TABLE [dbo].[CHUYENMON]
ADD CONSTRAINT [FK_CHUYENMON_CHITIETMONHOC]
    FOREIGN KEY ([CHITIETMONHOCs_ChiTietMonHocId])
    REFERENCES [dbo].[CHITIETMONHOCs]
        ([ChiTietMonHocId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [GIAOVIENs_UserId] in table 'CHUYENMON'
ALTER TABLE [dbo].[CHUYENMON]
ADD CONSTRAINT [FK_CHUYENMON_GIAOVIEN]
    FOREIGN KEY ([GIAOVIENs_UserId])
    REFERENCES [dbo].[USERs_GIAOVIEN]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CHUYENMON_GIAOVIEN'
CREATE INDEX [IX_FK_CHUYENMON_GIAOVIEN]
ON [dbo].[CHUYENMON]
    ([GIAOVIENs_UserId]);
GO

-- Creating foreign key on [UserId] in table 'USERs_GIAOVIEN'
ALTER TABLE [dbo].[USERs_GIAOVIEN]
ADD CONSTRAINT [FK_GIAOVIEN_inherits_USER]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[USERs]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UserId] in table 'USERs_HOCSINH'
ALTER TABLE [dbo].[USERs_HOCSINH]
ADD CONSTRAINT [FK_HOCSINH_inherits_USER]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[USERs]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UserId] in table 'USERs_NHANVIEN'
ALTER TABLE [dbo].[USERs_NHANVIEN]
ADD CONSTRAINT [FK_NHANVIEN_inherits_USER]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[USERs]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------